package com.crm.dao;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.crm.entity.AttachmentHistory;
import com.crm.entity.Business;
import com.crm.entity.PO;
import com.crm.util.ApplicationContext;

public class AttachmentHistoryDAO {
	protected final Log logger = LogFactory.getLog(getClass());
	
	public  int insert(AttachmentHistory ah)throws Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			Transaction tx=null;
			try {
			     tx = session.beginTransaction();
				session.saveOrUpdate(ah);
				tx.commit();
				return 0;
			}
			catch (Exception ex) {
			     if (tx!=null) tx.rollback();
			     throw ex;
			}
			finally {
			     session.close();
			}
		}
	public  int update(AttachmentHistory c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
			session.saveOrUpdate(c);
			tx.commit();
			return 0;
		}
		catch (Exception ex) {
		     if (tx!=null) tx.rollback();
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  AttachmentHistory getAttachmentHistoryDet(int busid,int AttachmentHistoryno) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
				Query query=session.createQuery("from AttachmentHistory WHERE business_id=:busid and AttachmentHistory_no=:id");
				query.setInteger("busid",busid);
				query.setInteger("id",AttachmentHistoryno );
				Object ob=query.uniqueResult();
				AttachmentHistory u=(AttachmentHistory)ob;
				return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}

	public  AttachmentHistory validatCredentials(String user, String pass,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from AttachmentHistory where email_id=:email and pwd=:pass and business_id=:busid");
			query.setString("email", user);
			query.setString("pass", pass);
			query.setInteger("busid", busid);
			AttachmentHistory u = (AttachmentHistory) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}

	public AttachmentHistory getAttachmentHistoryLoginDetails(String email) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from AttachmentHistory where email_id=:email");
			query.setString("email", email);
			AttachmentHistory u = (AttachmentHistory) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public AttachmentHistory getAttachmentHistoryById(int id) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from AttachmentHistory where id=:id");
			query.setInteger("id",id);
			AttachmentHistory u = (AttachmentHistory) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public AttachmentHistory getAttachmentHistoryByName(int busid,String fileName) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from AttachmentHistory where business_id=:busid and file_name=:fileName");
			query.setInteger("busid",busid);
			query.setString("fileName",fileName);		
			AttachmentHistory u = (AttachmentHistory) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public AttachmentHistory getAttachmentHistoryByQuotation(int busid,String fileName,String refno) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from AttachmentHistory where business_id=:busid and file_name=:fileName and refno=:refno");
			logger.info("the executed string is"+query);
			query.setInteger("busid",busid);
			query.setString("fileName",fileName);	
			query.setString("refno", refno);
			AttachmentHistory u = (AttachmentHistory) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public AttachmentHistory getAttachmentHistoryByRefno(int busid,String fileName,String refno) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from AttachmentHistory where business_id=:busid and file_name=:fileName and refno=:refno order by id");
			logger.info("the executed string is"+query);
			query.setInteger("busid",busid);
			query.setString("fileName",fileName);	
			query.setString("refno", refno);
			query.setMaxResults(1);
			AttachmentHistory u = (AttachmentHistory) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public AttachmentHistory getAttachmentHistoryByEnqno(int busid,String fileName,String enqno) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("From AttachmentHistory  where id=(select max(id) from AttachmentHistory where business_id=:busid and file_name=:fileName and enqno=:enqno)");
			logger.info("the executed string is"+query);
			query.setInteger("busid",busid);
			query.setString("fileName",fileName);	
			query.setString("enqno", enqno);
			AttachmentHistory u = (AttachmentHistory) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public AttachmentHistory getAttachmentHistoryByInvoice(int busid,String fileName,String refno) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from AttachmentHistory where business_id=:busid and file_name=:fileName and refno=:refno");
			logger.info("the executed string is"+query);
			query.setInteger("busid",busid);
			query.setString("fileName",fileName);	
			query.setString("refno", refno);
			AttachmentHistory u = (AttachmentHistory) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  List<AttachmentHistory> getAllPi(int busid,String filename)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="From AttachmentHistory where  business_id=:busid and file_name=:filename order by createddate";
			Query qry=session.createQuery(qtr);
			qry.setInteger("busid", busid);
			qry.setString("filename",filename);
			List<AttachmentHistory> qresult=qry.list();
			return qresult;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}

	}

	public AttachmentHistory getAttachmentHistoryByPI(int id) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from AttachmentHistory where id=:id");
			logger.info("the executed string is"+query);
			query.setInteger("id",id);
			AttachmentHistory u = (AttachmentHistory) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
}
